#!python3
# xlwt_demo.py 使用xlwt模块写excel
import datetime

import xlwt


def writeExcel():
    # 创建一个workbook 设置编码
    workbook = xlwt.Workbook(encoding='utf-8')
    # 创建一个worksheet
    worksheet = workbook.add_sheet('你好a')

    style = xlwt.XFStyle()  # 初始化样式

    # 字体
    font = xlwt.Font()
    font.name = 'Times New Roman'
    font.bold = True  # 黑体
    font.underline = True  # 下划线
    font.italic = True  # 斜体字
    font.colour_index = 10  # 字体颜色
    style.font = font  # 设定样式

    # 背景
    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
    pattern.pattern_fore_colour = 5  # May be: 8 through 63
    style.pattern = pattern

    # 边框
    borders = xlwt.Borders()
    borders.left = xlwt.Borders.DASHED  # DASHED-虚线、NO_LINE-无、THIN-实线
    borders.right = xlwt.Borders.DASHED
    borders.top = xlwt.Borders.DASHED
    borders.bottom = xlwt.Borders.DASHED
    borders.left_colour = 0xFF
    borders.right_colour = 0xFF
    borders.top_colour = 0xFF
    borders.bottom_colour = 0xFF
    style.borders = borders

    # 对齐方式
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_RIGHT
    alignment.vert = xlwt.Alignment.VERT_CENTER
    style.alignment = alignment

    worksheet.write(0, 0, 'Unformatted value')  # 不带样式的写入 row, column, value
    worksheet.write(0, 2, 'Formatted value', style)  # 带样式的写入
    worksheet.write(1, 0, 'Formatted value', style)  # 带样式的写入
    worksheet.write(3, 3, 'Formatted value', style)  # 带样式的写入

    # 合并单元格：（3，6） （5，8）
    worksheet.write_merge(3, 5, 6, 8, 'Second Merge', style) # row1, row2, column1, column2

    # 添加超链接
    worksheet.write(8, 3, xlwt.Formula('HYPERLINK("http://www.baidu.com";"百度（baidu）")'))

    # 添加公式
    worksheet.write(9, 3, 5)  # row, column, value
    worksheet.write(10, 3, 2)
    worksheet.write(11, 2, '乘积')
    worksheet.write(11, 3, xlwt.Formula('D10*D11'))
    worksheet.write(12, 2, '求和')
    worksheet.write(12, 3, xlwt.Formula('SUM(D10,D11)'))

    # 添加日期
    style = xlwt.XFStyle()
    # D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
    style.num_format_str = 'M/D/YY'
    worksheet.write(15, 0, datetime.datetime.now(), style)

    # 设置列宽
    worksheet.col(0).width = 256 * 20  # 20个字符宽度

    # 设置行高
    tall_style = xlwt.easyxf('font:height 720')
    first_row = worksheet.row(0)
    first_row.set_style(tall_style)

    # 保存
    workbook.save('./files/xlwt_test.xls')


if __name__ == '__main__':
    writeExcel()
